package com.mdsi.secretary;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mdsi.models.ConnectionManager;
import com.mdsi.models.Student;


/**
 * Servlet implementation class SecretaryAbsence
 */
@WebServlet("/User/Secretary/Absences")
public class SecretaryAbsence extends HttpServlet {
	private static final long serialVersionUID = 1L;
	int choix = 0;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SecretaryAbsence() {
        super();
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		if (request.getParameter("req") == null) {
			// Si le paramètre est manquant, on redirige
			response.sendRedirect(request.getContextPath() + "/User/Secretary") ;
			return ;
		}
		try {
			String req = request.getParameter("req");
			Connection conn = ConnectionManager.getConnection() ;
			PreparedStatement st = null ;
			ResultSet result = null ;
			String sqlRequest = "" ;
			if (req.equals("trois_absences")) {
				choix = 1;
				// La liste des étudiants ayant plus de 3 absences non justifiées dans des TDs et TPs
					sqlRequest = "SELECT DISTINCT s.ID_STUDENT, s.NUM, s.FIRSTNAME, s.LASTNAME, s.EMAIL, s.BIRTHDATE, s.NATIONALITY "
						+ "FROM student s, ("
							+ "SELECT a.student.num AS num, COUNT (a.student.num) AS nbr_abs "
							+ "FROM absence a "
							+ "WHERE (a.lesson.room.type_room = 'TD' OR a.lesson.room.type_room = 'TP') "
								+ "AND a.justify = 'N' "
							+ "GROUP BY a.student.num "
							+ "HAVING count(a.student) >= 3"
							+ ") count_absence "
						+ "WHERE count_absence.num = s.num";
			
			}
		
			else if (req.equals("tjr_absent")) {
				choix = 2;
				// La liste des étudiants qui ont été toujours absents pour toutes les matières (CM, TD et TP)
				sqlRequest = "SELECT DISTINCT S.ID_STUDENT, S.NUM, S.FIRSTNAME, S.LASTNAME, S.EMAIL, S.BIRTHDATE, S.NATIONALITY "
						+ "FROM student s, "
							+ "(SELECT a.student.num AS num, COUNT (a.student.num) AS nbr_abs "
							+ "FROM absence a "
							+ "GROUP BY a.student.num"
							+ ") count_absence,"
							+ "(SELECT s.num AS num, COUNT (l.id_lesson) AS nbr_total "
							+ "FROM student s, register r,lesson l "
							+ "WHERE s.id_student =  r.student.id_student "
								+ "AND l.subject = r.subject "
							+ "GROUP BY s.num"
							+ ") count_total "
						+ "WHERE count_absence.num = s.num "
						+ "AND count_absence.nbr_abs = count_total.nbr_total";
				
				
			}
			
			else if (req.equals("tjr_present")) {
				choix = 3;
				
				// La liste des étudiants qui ne se sont jamais absentés pour toutes les matières
				sqlRequest =  "SELECT DISTINCT S.ID_STUDENT, S.NUM, S.FIRSTNAME, S.LASTNAME, S.EMAIL, S.BIRTHDATE, S.NATIONALITY "
						+ "FROM student s, register r "
						+ "WHERE s.id_student =  r.student.id_student "
							+ "AND s.num NOT IN ( "
								+ "SELECT DISTINCT a.student.num "
								+ "FROM absence a"
								+ ") "
						+ "ORDER BY s.num, s.firstname, s.lastname";
				
			}
			else if (req.equals("trois_absences_a_un_cours")) {
				choix = 4;
				// La liste des étudiants qui ont au moins 3 absences non justifiées dans au moins un des cours (TD et TP)
				sqlRequest = "SELECT DISTINCT S.ID_STUDENT, S.NUM, S.FIRSTNAME, S.LASTNAME, S.EMAIL, S.BIRTHDATE, S.NATIONALITY "
						+ "FROM student s, "
							+ "(SELECT a.student.num AS num, a.lesson.subject, COUNT (*) AS nbr_abs "
							+ "FROM absence a "
							+ "WHERE (a.lesson.room.type_room = 'TD' OR a.lesson.room.type_room = 'TP') "
								+ "AND a.justify = 'N' "
							+ "GROUP BY a.student.num, a.lesson.subject "
							+ "HAVING COUNT (a.student) >=3"
							+ ") count_absence "
						+ "WHERE count_absence.num = s.num";

			}
			else {
				// requête incorrect, on redirige
				response.sendRedirect(request.getContextPath() + "/User/Secretary") ;
				return ;
			}
			
			
			
			st = conn.prepareStatement(sqlRequest) ;
			result = st.executeQuery() ;
			ArrayList <Student> students = new ArrayList <Student> () ;
			while (result.next()) {
				Calendar birthdate = GregorianCalendar.getInstance() ;
				birthdate.setTime(result.getDate("BIRTHDATE")) ;
				students.add(new Student(
					result.getLong("ID_STUDENT"), 
					result.getString("FIRSTNAME"), 
					result.getString("LASTNAME"),
					result.getString("EMAIL"), 
					result.getLong("NUM"), 
					birthdate, 
					result.getString("NATIONALITY"))) ;
			}
			
			conn.close () ;
			getServletContext().setAttribute("students", students) ;
			getServletContext().setAttribute("choix", choix) ;
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		this.getServletContext().getRequestDispatcher("/WEB-INF/secretary/absences.jsp").forward(request, response);			
		
	}

}
